﻿using Abp.Web;
using Dapper;
using NHibernate;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Abp.Dapper
{
    public static class SqlDapperExtension
    {
        /// <summary>
        /// 复杂分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static List<T> QueryPaged<T>(this ISession session, Pagination pagination, string sql, DynamicParameters parameters, string orderBy)
        {
            if (!string.IsNullOrWhiteSpace(pagination.Sort) && !string.IsNullOrWhiteSpace(pagination.Order))
                orderBy = pagination.GetOrderBy();
            string sqls = @"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS ROWID,* FROM ({1}  ) AS T ) AS TT WHERE ROWID BETWEEN @StartRecordIndex AND @EndRecordIndex
                SELECT COUNT(*)  FROM ({1} ) AS T";

            sqls = string.Format(sqls, orderBy, sql);

            parameters.Add("@StartRecordIndex", pagination.GetStartRecordIndex() + 1, DbType.Int32);
            parameters.Add("@EndRecordIndex", pagination.GetEndRecordIndex(), DbType.Int32);

            session.Transaction.Dispose();
            var mutil = session.Connection.QueryMultiple(sqls, parameters);
            var data = mutil.Read<T>().ToList();
            var total = mutil.Read<int>().FirstOrDefault();

            pagination.Total = total;
            return data;


        }

        /// <summary>
        /// 复杂分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static List<T> QueryPaged<T>(this IDbConnection connection, Pagination pagination, string sql, DynamicParameters parameters, string orderBy)
        {
            if (!string.IsNullOrWhiteSpace(pagination.Sort) && !string.IsNullOrWhiteSpace(pagination.Order))
                orderBy = pagination.GetOrderBy();
            string sqls = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderBy + ") AS ROWID,* FROM (" + sql + @") AS T ) AS TT WHERE ROWID BETWEEN @StartRecordIndex AND @EndRecordIndex
                SELECT COUNT(*)  FROM (" + sql + ") AS T";

            if (parameters == null)
                parameters = new DynamicParameters();
            parameters.Add("@StartRecordIndex", pagination.GetStartRecordIndex() + 1, DbType.Int32);
            parameters.Add("@EndRecordIndex", pagination.GetEndRecordIndex(), DbType.Int32);

            var mutil = connection.QueryMultiple(sqls, parameters);

            var data = mutil.Read<T>().ToList();
            var total = mutil.Read<int>().FirstOrDefault();
            connection.Close();
            pagination.Total = total;
            return data;


        }


        /// <summary>
        /// 批量写入数据【自带事物提交】
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dataTable"></param>
        public static void BulkCopy(this SqlConnection connection, DataTable dataTable)
        {

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            SqlTransaction transaction = (SqlTransaction)connection.BeginTransaction();
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
            try
            {
                sqlBulkCopy.DestinationTableName = dataTable.TableName;
                sqlBulkCopy.WriteToServer(dataTable);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 批量写入数据【自带事物提交】
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dataset"></param>
        public static void BulkCopy(this SqlConnection connection, DataSet dataset)
        {

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            SqlTransaction transaction = (SqlTransaction)connection.BeginTransaction();
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
            try
            {
                foreach (DataTable dataTable in dataset.Tables)
                {
                    sqlBulkCopy.DestinationTableName = dataTable.TableName;
                    sqlBulkCopy.WriteToServer(dataTable);
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 批量写入数据【调用时手动提交事务方便和其他方法混合使用】
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="dataTable"></param>
        public static void BulkCopy(this SqlTransaction transaction, DataTable dataTable)
        {
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.CheckConstraints, transaction);

            sqlBulkCopy.DestinationTableName = dataTable.TableName;
            sqlBulkCopy.WriteToServer(dataTable);
        }
        /// <summary>
        /// 批量写入数据【调用时手动提交事务方便和其他方法混合使用】
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="dataset"></param>
        public static void BulkCopy(this SqlTransaction transaction, DataSet dataset)
        {

            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.CheckConstraints, transaction);

            foreach (DataTable dataTable in dataset.Tables)
            {
                sqlBulkCopy.DestinationTableName = dataTable.TableName;
                sqlBulkCopy.WriteToServer(dataTable);
            }


        }
    }
}
